package dao.imp;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.Goods;
import dao.DaoHelper;
import dao.GoodsDao;

public class GoodsDaoImp implements GoodsDao {

	private static GoodsDaoImp goodsDao = new GoodsDaoImp();
	private static DaoHelper daoHelper=DaoHelperImp.getBaseDaoInstance();

	private GoodsDaoImp(){}

	public static GoodsDaoImp getInstance()
	{
		return goodsDao;
	}

	@Override
	public boolean save(Goods goods) {
		// TODO Auto-generated method stub
		Connection con=daoHelper.getConnection();
		PreparedStatement stmt=null;
		boolean success = true;

		try {	
			stmt=con.prepareStatement("insert into goods value(?, ?, ?, ?, ?)");
			stmt.setInt(1, goods.getId());
			stmt.setString(2, goods.getName());
			stmt.setFloat(3, goods.getPrice());
			stmt.setInt(4, goods.getQuantity());
			stmt.setDate(5, goods.getDate());
			stmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			success = false;
		} finally {
			daoHelper.closeConnection(con);
			daoHelper.closePreparedStatement(stmt);
		}

		return success;

	}

	@Override
	public boolean updateByIdDate(Goods goods) {
		// TODO Auto-generated method stub
		Connection con=daoHelper.getConnection();
		PreparedStatement stmt=null;
		boolean success = true;

		try {	
			stmt=con.prepareStatement("update goods set name = ?, price = ?, quantity = ? where id = ? and date = ?");
			stmt.setString(1, goods.getName());
			stmt.setFloat(2, goods.getPrice());
			stmt.setInt(3, goods.getQuantity());
			stmt.setInt(4, goods.getId());
			stmt.setDate(5, goods.getDate());
			stmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			success = false;
		} finally {
			daoHelper.closeConnection(con);
			daoHelper.closePreparedStatement(stmt);
		}

		return success;

	}

	@Override
	public boolean updateQuantityByIdDate(Goods goods) {
		// TODO Auto-generated method stub
		Connection con=daoHelper.getConnection();
		PreparedStatement stmt=null;
		boolean success = true;

		try {	
			stmt=con.prepareStatement("update goods set quantity = ? where id = ? and date = ?");
			stmt.setInt(1, goods.getQuantity());
			stmt.setInt(2, goods.getId());
			stmt.setDate(3, goods.getDate());
			stmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			success = false;
		} finally {
			daoHelper.closeConnection(con);
			daoHelper.closePreparedStatement(stmt);
		}

		return success;

	}

	@Override
	public List<Goods> findAll() {
		Connection con=daoHelper.getConnection();
		PreparedStatement stmt=null;
		ResultSet result=null;
		int i = 0;
		String n = null;
		float p = 0;
		int q = 0;
		Date d = null;
		Goods goods = null;
		ArrayList<Goods> list = new ArrayList<Goods>();

		try {	
			stmt=con.prepareStatement("select * from goods where quantity > 0");
			result=stmt.executeQuery();
			while (result.next()) {
				i = result.getInt(1);
				n = result.getString(2);
				p = result.getFloat(3);
				q = result.getInt(4);
				d = result.getDate(5);
				goods = new Goods(i, n, p, q, d);
				list.add(goods);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			daoHelper.closeConnection(con);
			daoHelper.closePreparedStatement(stmt);
			daoHelper.closeResult(result);
		}

		return list;

	}

	@Override
	public List<Goods> find(Date date) {
		// TODO Auto-generated method stub
		Connection con=daoHelper.getConnection();
		PreparedStatement stmt=null;
		ResultSet result=null;
		int i = 0;
		String n = null;
		float p = 0;
		int q = 0;
		Date d = null;
		Goods goods = null;
		ArrayList<Goods> list = new ArrayList<Goods>();

		try {	
			stmt=con.prepareStatement("select * from goods where date = ? and quantity <> 0");
			stmt.setDate(1, date);
			result=stmt.executeQuery();
			while (result.next()) {
				i = result.getInt(1);
				n = result.getString(2);
				p = result.getFloat(3);
				q = result.getInt(4);
				d = result.getDate(5);
				goods = new Goods(i, n, p, q, d);
				list.add(goods);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			daoHelper.closeConnection(con);
			daoHelper.closePreparedStatement(stmt);
			daoHelper.closeResult(result);
		}

		return list;

	}

	@Override
	public Goods find(int id, Date date) {
		Connection con=daoHelper.getConnection();
		PreparedStatement stmt=null;
		ResultSet result=null;
		int i = 0;
		String n = null;
		float p = 0;
		int q = 0;
		Date d = null;
		Goods goods = null;

		try {	
			stmt=con.prepareStatement("select * from goods where date = ? and id = ? and quantity <> 0");
			stmt.setDate(1, date);
			stmt.setInt(2, id);
			result=stmt.executeQuery();
			if (result.next()) {
				i = result.getInt(1);
				n = result.getString(2);
				p = result.getFloat(3);
				q = result.getInt(4);
				d = result.getDate(5);
				goods = new Goods(i, n, p, q, d);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			daoHelper.closeConnection(con);
			daoHelper.closePreparedStatement(stmt);
			daoHelper.closeResult(result);
		}

		return goods;
	}

	@Override
	public boolean removeByIdDate(int id, Date date) {
		// TODO Auto-generated method stub
		Connection con=daoHelper.getConnection();
		PreparedStatement stmt=null;
		boolean success = true;

		try {	
			stmt=con.prepareStatement("delete from goods where id = ? and date = ?");
			stmt.setInt(1, id);
			stmt.setDate(2, date);
			stmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			success = false;
		} finally {
			daoHelper.closeConnection(con);
			daoHelper.closePreparedStatement(stmt);
		}

		return success;

	}

}
